package jgespres;

import java.sql.*;
import java.util.logging.*;

/**
 * Utilitaires.
 * 
 * @author Philippe Guerreiro
 * @version 2.0
 */
public class DBTools {
    /**
     * Convertion de la base de données en v2
     * 
     * @param  connect      Connection à la base
     */
    public static void convertDBv1to2(Connection connect) {
        try {
            connect.setAutoCommit(false);
            Statement stmt = connect.createStatement();
            
            // Convertion base de données
            stmt.executeUpdate(JBaseDlg.CRTPLN);
            stmt.executeUpdate("INSERT INTO Planning2 SELECT b.date, a.idStd, a.extraStd, a.session, a.idThm, a.idHlp, a.extraHlp, c.status, false, a.extra FROM Planning a JOIN Themes b ON a.idThm = b.id LEFT JOIN Status c ON b.date = c.date and a.idStd = c.idStd");
            stmt.executeUpdate("INSERT INTO Planning2 (date, idStd, status) SELECT c.date, c.idStd, c.status FROM status c LEFT JOIN planning2 a ON a.date = c.date and a.idStd = c.idStd WHERE a.date is null");
            stmt.executeUpdate("INSERT INTO Planning2 (date, separator) SELECT date, true FROM Themes WHERE libs = '00'");
            stmt.executeUpdate(JBaseDlg.CRTTBL);
            stmt.executeUpdate("INSERT INTO Table2 VALUES('CSTATUS', '2', 'Busy;777777')");
            stmt.executeUpdate("INSERT INTO Table2 VALUES('CSTATUS', '3', 'Unavailable;000000')");
            stmt.executeUpdate("INSERT INTO Table2 SELECT idTable, cast(keyTable as CHAR(15)), text FROM Table WHERE idTable = 'SESSION'");
            stmt.executeUpdate("INSERT INTO Table2 SELECT 'IDENT', rtrim(idTable) + ';' + cast(keyTable as CHAR(15)), text FROM Table WHERE idTable <> 'SESSION'");
            stmt.executeUpdate(JBaseDlg.CRTARC);
            
            connect.commit();
            connect.setAutoCommit(true);
        }
        catch (Exception e) {
            try {
                connect.rollback();
                connect.setAutoCommit(true);
            }
            catch (Exception e2) {
                System.out.println("Erreur grave lors de la convertion de la base v1->v2 !");
                System.exit(1);
            }
            System.out.println("Erreur lors de la convertion de la base v1->v2 !");
            e.printStackTrace();
        }
    }
    
    /**
     * Archivage de la base de données
     * 
     * @param  connect      Connection à la base
     * @param  arcDate      Date d'archivage
     * @param  logger       logger courant
     */
    public static void archiveDB(Connection connect, java.sql.Date arcDate, Logger logger) {
        int res = 0;
        // Archivage du planning
        try {
            connect.setAutoCommit(false);
            Statement stmt = connect.createStatement();

            res = stmt.executeUpdate("INSERT INTO Archive SELECT b.date, b.libS, b.libL, b.libXL, a.session, a.idStd, a.extraStd, a.idHlp, a.extraHlp, a.extra, ifnull(c.status, 0) FROM Planning a JOIN Themes b ON a.idThm = b.id LEFT JOIN Status c ON b.date = c.date and a.idStd = c.idStd WHERE b.date < '"+arcDate+"'");
            logger.log(Level.CONFIG, "ARCHIVAGE :"+res+" enregistrements du planning archivés");
            res = stmt.executeUpdate("INSERT INTO Archive (date, status) SELECT date, status FROM Status a WHERE a.idStd = 0 and a.date < '"+arcDate+"'");
            res = stmt.executeUpdate("DELETE FROM Planning WHERE idThm in (SELECT id FROM Themes WHERE date < '"+arcDate+"')");
            logger.log(Level.CONFIG, "ARCHIVAGE :"+res+" enregistrements du planning supprimés");
            res = stmt.executeUpdate("DELETE FROM Themes WHERE date < '"+arcDate+"'");
            logger.log(Level.CONFIG, "ARCHIVAGE :"+res+" thèmes supprimés");
            res = stmt.executeUpdate("DELETE FROM Status WHERE date < '"+arcDate+"'");
            
            connect.commit();
            connect.setAutoCommit(true);
        }
        catch (Exception e) {
            try {
                connect.rollback();
                connect.setAutoCommit(true);
            }
            catch (Exception e2) {
                logger.log(Level.SEVERE, "Erreur grave lors de l'archivage !");
                System.exit(1);
            }
            logger.log(Level.SEVERE, "Erreur lors de l'archivage !");
            e.printStackTrace();
        }
    }

    /**
     * Purge de la base de données
     * 
     * @param  connect      Connection à la base
     */
    public static void cleanDB(Connection connect) {
    }
}
